﻿

--创建（Create）、更新（Update）、读取（Read）
CREATE PROCEDURE [dbo].[RDIFramework_PiUser_CURD]
(	@id int	 = null
   ,@Code nvarchar(50)
   ,@UserName nvarchar(50)
   ,@RealName nvarchar(50)
   ,@RoleId int
   ,@UserFrom nvarchar(50)
   ,@WorkCategory nvarchar(50)
   ,@CompanyId int
   ,@CompanyName nvarchar(50)
   ,@DepartmentId int
   ,@DepartmentName nvarchar(50)
   ,@WorkgroupId int
   ,@WorkgroupName nvarchar(50)
   ,@Gender nvarchar(6)
   ,@Mobile nvarchar(50)
   ,@Telephone nvarchar(30)
   ,@Birthday date
   ,@Duty nvarchar(50)
   ,@Title nvarchar(50)
   ,@UserPassword nvarchar(200)
   ,@ChangePasswordDate datetime
   ,@QICQ nvarchar(50)
   ,@Email nvarchar(200)
   ,@Lang nvarchar(50)
   ,@Theme nvarchar(50)
   ,@AllowStartTime datetime
   ,@AllowEndTime datetime
   ,@LockStartDate date
   ,@LockEndDate date
   ,@FirstVisit datetime
   ,@PreviousVisit datetime
   ,@LastVisit datetime
   ,@LogOnCount int
   ,@IsStaff int
   ,@AuditStatus nvarchar(50)
   ,@IsVisible int = 1
   ,@UserOnLine int
   ,@IPAddress nvarchar(50)
   ,@MACAddress nvarchar(50)
   ,@HomeAddress nvarchar(200)
   ,@OpenId nvarchar(50)
   ,@Question nvarchar(50)
   ,@AnswerQuestion nvarchar(50)
   ,@UserAddress nvarchar(200)
   ,@DeleteMark int = 0
   ,@Enabled int =1
   ,@SortCode int
   ,@Description nvarchar(800)
   ,@CreateOn datetime = getdate
   ,@CreateUserId nvarchar(50)
   ,@CreateBy nvarchar(50)
   ,@ModifiedOn datetime = getdate
   ,@ModifyUserId nvarchar(50)
   ,@ModifiedBy nvarchar(50)
   ,@OperationType nvarchar(30)				--操作类型,ADD：增加数据、SELECT:查询数据、UPDATE：更新数据
   ,@SuccessMark INT = 0 OUTPUT		        --成功标志 >0：操作成功 <=0,操作失败
   ,@ReturnInfo nvarchar(500) = '' OUTPUT   --操作提示信息 
)
AS
BEGIN
	SET NOCOUNT ON
	IF @OperationType IS NULL
	BEGIN
		RAISERROR ( '操作类型不能为空！', 16, 1 )
		SELECT @ReturnInfo  =  '操作类型不能为空！'
		SELECT @SuccessMark = 0
		ROLLBACK TRAN
		RETURN
	END
	
	IF @OperationType IN('UPDATE','DELETE','SETDELMARK')
	BEGIN
		IF @Id IS NULL
		BEGIN
			RAISERROR ( '用户主键为空！', 16, 1 )
			SELECT @ReturnInfo =  '用户主键为空！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
		END
	END
	
	--1、查询数据
	IF @OperationType = 'SELECT' --查询数据
	BEGIN
		SELECT  Id, Code, UserName, RealName, RoleId, UserFrom, WorkCategory, CompanyId, CompanyName, DepartmentId, DepartmentName, WorkgroupId, 
                WorkgroupName, Gender, Mobile, Telephone, Birthday, Duty, Title, UserPassword, ChangePasswordDate, QICQ, Email, Lang, Theme, AllowStartTime, 
                AllowEndTime, LockStartDate, LockEndDate, FirstVisit, PreviousVisit, LastVisit, LogOnCount, IsStaff, AuditStatus, IsVisible, UserOnLine, IPAddress, 
                MACAddress, HomeAddress, OpenId, Question, AnswerQuestion, UserAddress, DeleteMark, Enabled, SortCode, Description, CreateOn, 
                CreateUserId, CreateBy, ModifiedOn, ModifyUserId, ModifiedBy,
                 (SELECT Realname
                  FROM  dbo.PiRole AS AliasRole
                  WHERE  (AliasUser.RoleId = Id)) AS RoleName
		FROM  dbo.PiUser AS AliasUser
		WHERE (DeleteMark = 0)
	END
	
	--2、新增数据
	IF @OperationType = 'ADD' --增加数据
	BEGIN
		
		IF EXISTS(SELECT 1 FROM dbo.PiUser WHERE UserName = @UserName AND DeleteMark = 0)
		BEGIN
			SELECT @ReturnInfo='已存在登录名相同的用户！'
			SELECT @SuccessMark = 0
			RETURN  
		END
		
		BEGIN TRAN
		INSERT INTO dbo.PiUser(Code,UserName,RealName,RoleId,UserFrom,WorkCategory,CompanyId,CompanyName
				  ,DepartmentId,DepartmentName,WorkgroupId,WorkgroupName,Gender,Mobile,Telephone
				  ,Birthday,Duty,Title,UserPassword,ChangePasswordDate,QICQ,Email,Lang,Theme,AllowStartTime
				  ,AllowEndTime,LockStartDate,LockEndDate,FirstVisit,PreviousVisit,LastVisit,LogOnCount,IsStaff
				  ,AuditStatus,IsVisible,UserOnLine,IPAddress,MACAddress,HomeAddress,OpenId,Question,AnswerQuestion
				  ,UserAddress,DeleteMark,Enabled,SortCode,Description,CreateOn,CreateUserId,CreateBy
				  ,ModifiedOn,ModifyUserId,ModifiedBy)
		VALUES
			   (@Code,@UserName,@RealName,@RoleId,@UserFrom, @WorkCategory,@CompanyId,@CompanyName,
			   @DepartmentId, @DepartmentName,@WorkgroupId,@WorkgroupName,@Gender,@Mobile,@Telephone,
			   @Birthday, @Duty, @Title, @UserPassword,@ChangePasswordDate, @QICQ,@Email,@Lang, @Theme,
			   @AllowStartTime,@AllowEndTime,@LockStartDate,@LockEndDate,@FirstVisit,@PreviousVisit,
			   @LastVisit,@LogOnCount,@IsStaff,@AuditStatus,@IsVisible,@UserOnLine,@IPAddress,@MACAddress,
			   @HomeAddress,@OpenId,@Question,@AnswerQuestion,@UserAddress,@DeleteMark,@Enabled,
			   @SortCode,@Description,@CreateOn,@CreateUserId,@CreateBy,@ModifiedOn,@ModifyUserId,@ModifiedBy)	
			   
		IF @@ROWCOUNT > 0 AND @@ERROR=0    
	    BEGIN 
			SELECT @ReturnInfo='成功新增用户数据！'  
			SELECT @SuccessMark = 1    
			COMMIT TRAN
			RETURN
	    END    
		ELSE
		BEGIN
			SELECT @ReturnInfo='新增用户数据失败！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
			RETURN
		END
	END
	
	--3、修改数据
	IF @OperationType = 'UPDATE' --修改数据
	BEGIN		
		IF EXISTS(SELECT 1 FROM dbo.PiUser WHERE UserName = @UserName AND Id <> @Id AND DeleteMark = 0)
		BEGIN
			SELECT @ReturnInfo='已存在登录名相同的用户！'
			SELECT @SuccessMark = 0
			RETURN  
		END
		
		IF @ModifiedOn IS NULL
		BEGIN
			SELECT @ModifiedOn = GETDATE()
		END
		
		BEGIN TRAN
		UPDATE dbo.PiUser
	    SET	   Code = @Code,UserName = @UserName,RealName = @RealName,RoleId = @RoleId,UserFrom = @UserFrom,WorkCategory = @WorkCategory
			  ,CompanyId = @CompanyId,CompanyName = @CompanyName, DepartmentId = @DepartmentId, DepartmentName = @DepartmentName,WorkgroupId = @WorkgroupId
			  ,WorkgroupName = @WorkgroupName,Gender = @Gender,Mobile = @Mobile,Telephone = @Telephone,Birthday = @Birthday,Duty = @Duty,Title = @Title
			  ,UserPassword = @UserPassword, ChangePasswordDate = @ChangePasswordDate,QICQ = @QICQ,Email = @Email, Lang = @Lang,Theme = @Theme
			  ,AllowStartTime = @AllowStartTime,AllowEndTime = @AllowEndTime,LockStartDate = @LockStartDate,LockEndDate = @LockEndDate,FirstVisit = @FirstVisit
			  ,PreviousVisit = @PreviousVisit,LastVisit = @LastVisit,LogOnCount = @LogOnCount, IsStaff = @IsStaff,AuditStatus = @AuditStatus,IsVisible = @IsVisible
			  ,UserOnLine = @UserOnLine,IPAddress = @IPAddress,MACAddress = @MACAddress,HomeAddress = @HomeAddress, OpenId = @OpenId,Question = @Question
			  ,AnswerQuestion = @AnswerQuestion,UserAddress = @UserAddress,DeleteMark = @DeleteMark,Enabled = @Enabled,SortCode = @SortCode
			  ,Description = @Description,CreateOn = @CreateOn,CreateUserId = @CreateUserId,CreateBy = @CreateBy,ModifiedOn = @ModifiedOn,ModifyUserId = @ModifyUserId,ModifiedBy = @ModifiedBy
		WHERE Id = @Id
		
		IF @@ROWCOUNT > 0 AND @@ERROR=0    
	    BEGIN 
			SELECT @ReturnInfo='成功修改用户数据！'   
			SELECT @SuccessMark = 1   
			COMMIT TRAN
			RETURN
	    END    
		ELSE
		BEGIN
			SELECT @ReturnInfo='修改用户数据失败！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
			RETURN
		END 
	END
	
END







